﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SCSService
{
    public class UserService
    {
        /// <summary>
        /// Get User by Id 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Security_User GetUserByID(int id)
        {
            using (var data = new DataDataContext())
            {
                var user = data.Security_Users.Where(p => p.UserID == id).FirstOrDefault();
                return user;
            }
        }

        /// <summary>
        /// Create user 
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int CreateUser(Security_User user)
        {
            using (var data = new DataDataContext())
            {
                data.Security_Users.InsertOnSubmit(user);
                data.SubmitChanges();

                return user.UserID;
            }
        }

        /// <summary>
        /// Create Customer
        /// </summary>
        /// <param name="customer"></param>
        /// <returns></returns>
        public int CreateCustomer(Customer customer)
        {
            using (var data = new DataDataContext())
            {
                data.Customers.InsertOnSubmit(customer);
                data.SubmitChanges();

                return customer.CustomerID;
            }
        }

        /// <summary>
        /// Update user
        /// </summary>
        /// <param name="user"></param>
        public void UpdateUser(Security_User user)
        {
            using (var data = new DataDataContext())
            {
                var userUpdate = data.Security_Users.Where(p => p.UserID == user.UserID).SingleOrDefault();

                userUpdate.UserName = user.UserName;
                userUpdate.Password = user.Password;
                userUpdate.RoleID = user.RoleID;
                userUpdate.Email = user.Email;
                userUpdate.UserStatus = user.UserStatus;
                userUpdate.UserDes = user.UserDes;
                userUpdate.RandomText = user.RandomText;
                userUpdate.UndefineOne = user.UndefineOne;
                userUpdate.UndefineTwo = user.UndefineTwo;
                userUpdate.LastModified = DateTime.Now;

                data.SubmitChanges();
            }
        }

        /// <summary>
        /// change password for user
        /// </summary>
        /// <param name="user"></param>
        public void ChangePassword(Security_User user)
        {
            using (var data = new DataDataContext())
            {
                BaseService baseSvc=new BaseService();
                var userUpdate = data.Security_Users.Where(p => p.UserID == user.UserID).SingleOrDefault();

                userUpdate.Password = baseSvc.EnMD5(user.Password);
                userUpdate.LastModified = DateTime.Now;
                data.SubmitChanges();
            }
        }

        /// <summary>
        /// Get list user by role id
        /// </summary>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public List<Security_User> GetListUserByRole(int roleId)
        {
            using (var data = new DataDataContext())
            {
                var item = data.Security_Users.Where(p => p.RoleID == roleId).ToList();
                return item;
            }
        }


        /// <summary>
        /// Login User
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public SCSService.Security_User LogInUser(string userName, string password)
        {
            using (var data = new DataDataContext())
            {
                BaseService baseSvc = new BaseService();
                var user = data.Security_Users.Where(p => p.UserName == userName && p.Password == baseSvc.EnMD5(password) && p.UserStatus==1).SingleOrDefault();
                return user;
            }
        }

        /// <summary>
        /// Get User by userName and Email
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="email"></param>
        /// <returns></returns>
        public SCSService.Security_User GetUserByUserNameAndEmail(string userName, string email)
        {
            using (var data = new DataDataContext())
            {
                var user = data.Security_Users.Where(p => p.UserName == userName && p.Email == email).SingleOrDefault();
                return user;
            }
        }

        /// <summary>
        /// Get Count User by Username
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public int GetUserByUserName(string userName)
        {
            using (var data = new DataDataContext())
            {
                var user = data.Security_Users.Where(p => p.UserName == userName);

                return user.Count();
            }
        }

        /// <summary>
        /// Get Count Website by domain
        /// </summary>
        /// <param name="domain"></param>
        /// <returns></returns>
        public int GetWebsiteByDomain(string domain)
        {
            using (var data = new DataDataContext())
            {
                var user = data.System_Websites.Where(p => p.domain == domain);

                return user.Count();
            }
        }

        /// <summary>
        /// Get count user by Email
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public int GetUserbyEmail(string email)
        {
            using (var data = new DataDataContext())
            {
                var user = data.Security_Users.Where(p => p.Email == email);
                return user.Count();
            }
        }
        /// <summary>
        /// Get Permission of User when login
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Security_Permission GetPermissionByUserID(int id)
        {
            using (var data = new DataDataContext())
            {
                var per = (from u in data.Security_Users
                           join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                           from t0 in e0.DefaultIfEmpty()
                           join rp in data.Security_Role_Permissions on t0.RoleID equals rp.RoleID into e1
                           from t1 in e1.DefaultIfEmpty()
                           join p in data.Security_Permissions on t1.PerID equals p.PerID into e2
                           from t2 in e2.DefaultIfEmpty()
                           where u.UserID == id
                           orderby t0.Priority ascending
                           select t2).FirstOrDefault();

                return per;
            }
        }

        /// <summary>
        /// Get Role top by user id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Security_Role GetRoleUser(int id)
        {
            using (var data = new DataDataContext())
            {
                var role = (from u in data.Security_Users
                            join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                            from t0 in e0.DefaultIfEmpty()
                            where u.UserID == id
                            orderby t0.Priority ascending
                            select t0).FirstOrDefault();

                return role;
            }
        }

        public Security_Role GetRoleSystemByUser(int id)
        {
            using (var data = new DataDataContext())
            {
                var role = (from u in data.Security_Users
                            join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                            from t0 in e0.DefaultIfEmpty()
                            where u.UserID == id && t0.SiteGuidCreated==null
                            orderby t0.Priority ascending
                            select t0).FirstOrDefault();

                return role;
            }
        }

        public Security_Role GetRoleLocalSiteByUser(int id,int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var role = (from u in data.Security_Users
                            join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                            from t0 in e0.DefaultIfEmpty()
                            where u.UserID == id && t0.SiteGuidCreated == siteGuid
                            orderby t0.Priority ascending
                            select t0).FirstOrDefault();

                return role;
            }
        }

        /// <summary>
        /// Create role on System
        /// </summary>
        /// <param name="role"></param>
        /// <returns></returns>
        public int CreateRole(Security_Role role)
        {
            using (var data = new DataDataContext())
            {
                data.Security_Roles.InsertOnSubmit(role);
                data.SubmitChanges();

                return role.RoleID;
            }
        }

        /// <summary>
        /// Update role
        /// </summary>
        /// <param name="role"></param>
        public void UpdateRole(Security_Role role)
        {
            using (var data = new DataDataContext())
            {
                var roleUpdate = data.Security_Roles.Where(p => p.RoleID == role.RoleID).SingleOrDefault();

                roleUpdate.RoleName = role.RoleName;
                roleUpdate.RoleDes = role.RoleDes;
                roleUpdate.RoleStatus = role.RoleStatus;

                data.SubmitChanges();
            }
        }

        /// <summary>
        /// Create Log when User LogIn
        /// </summary>
        /// <param name="log"></param>
        /// <returns></returns>
        public int CreateLog(Security_User_Log log)
        {
            using (var data = new DataDataContext())
            {
                data.Security_User_Logs.InsertOnSubmit(log);
                data.SubmitChanges();

                return log.LogID;
            }
        }

        /// <summary>
        /// Create Security User
        /// </summary>
        /// <param name="webUser"></param>
        public void CreateWebsiteUser(Security_Website_User webUser)
        {
            using (var data = new DataDataContext())
            {
                data.Security_Website_Users.InsertOnSubmit(webUser);
                data.SubmitChanges();

            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="webRole"></param>
        public void CreateWebsiteRole(Sercurity_Website_Role webRole)
        {
            using (var data = new DataDataContext())
            {
                data.Sercurity_Website_Roles.InsertOnSubmit(webRole);
                data.SubmitChanges();

            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="webPer"></param>
        public void CreateWebsitePermission(Security_Website_Permission webPer)
        {
            using (var data = new DataDataContext())
            {
                data.Security_Website_Permissions.InsertOnSubmit(webPer);
                data.SubmitChanges();

            }
        }
        /// <summary>
        /// Get Security role by id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public SCSService.Security_Role GetRoleByID(int id)
        {
            using (var data = new DataDataContext())
            {
                var role = (from p in data.Security_Roles
                            where p.RoleID == id
                            select p).FirstOrDefault();
                return role;
            }
        }


        /// <summary>
        /// Get Security per by id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public SCSService.Security_Permission GetPermissionByID(int id)
        {
            using (var data = new DataDataContext())
            {
                var per = (from p in data.Security_Permissions
                           where p.PerID == id
                           select p).FirstOrDefault();
                return per;
            }
        }

        /// <summary>
        /// Create Security Permission
        /// </summary>
        /// <param name="per"></param>
        /// <returns></returns>
        public int CreateSecurityPermission(SCSService.Security_Permission per)
        {
            using (var data = new DataDataContext())
            {
                data.Security_Permissions.InsertOnSubmit(per);
                data.SubmitChanges();

                return per.PerID;
            }
        }
        /// <summary>
        /// Update Permission by id
        /// </summary>
        /// <param name="per"></param>
        public void UpdateSecurityPermission(SCSService.Security_Permission per)
        {
            using (var data = new DataDataContext())
            {
                var perUpdate = data.Security_Permissions.Where(p => p.PerID == per.PerID).SingleOrDefault();

                perUpdate.PerName = per.PerName;
                perUpdate.PerDes = per.PerDes;
                perUpdate.ParentID = per.ParentID;
                perUpdate.PerStatus = per.PerStatus;

                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Delete Sercurity Permission
        /// </summary>
        /// <param name="id"></param>
        public void DeleteSecurityPermission(int id)
        {
            using (var data = new DataDataContext())
            {
                var perDelete = data.Security_Permissions.Where(p => p.PerID == id).SingleOrDefault();

                data.Security_Permissions.DeleteOnSubmit(perDelete);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Delete Sercurity role
        /// </summary>
        /// <param name="id"></param>
        public void DeleteSecurityRole(int id)
        {
            using (var data = new DataDataContext())
            {
                var roleDelete = data.Security_Roles.Where(p => p.RoleID == id).SingleOrDefault();

                data.Security_Roles.DeleteOnSubmit(roleDelete);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Get List Security Role
        /// </summary>
        /// <returns></returns>
        public List<SCSService.Security_Role> GetListRole(string RoleName, string RoleDes, byte? RoleStatus, int? Priority,int? siteGuid)
        {
            using (var data = new DataDataContext())
            {
                if (siteGuid != null)
                {
                    var list = (from p in data.Security_Roles
                                join swr in data.Sercurity_Website_Roles on p.RoleID equals swr.RoleID
                                where swr.SiteGuid==siteGuid
                                select p);

                    if (RoleName != string.Empty && RoleName != null)
                    {
                        list = list.Where(p => p.RoleName.Contains(RoleName));
                    }

                    if (RoleDes != string.Empty && RoleDes != null)
                    {
                        list = list.Where(p => p.RoleDes.Contains(RoleDes));
                    }

                    if (RoleStatus != null)
                    {
                        list = list.Where(p => p.RoleStatus == RoleStatus);
                    }

                    if (Priority != null)
                    {
                        list = list.Where(p => p.Priority == Priority);
                    }

                    return list.ToList();
                }
                else
                {
                    var list = (from p in data.Security_Roles
                                join swr in data.Sercurity_Website_Roles on p.RoleID equals swr.RoleID
                                select p);

                    if (RoleName != string.Empty && RoleName != null)
                    {
                        list = list.Where(p => p.RoleName.Contains(RoleName));
                    }

                    if (RoleDes != string.Empty && RoleDes != null)
                    {
                        list = list.Where(p => p.RoleDes.Contains(RoleDes));
                    }

                    if (RoleStatus != null)
                    {
                        list = list.Where(p => p.RoleStatus == RoleStatus);
                    }

                    if (Priority != null)
                    {
                        list = list.Where(p => p.Priority == Priority);
                    }

                    return list.ToList();
                }
                
            }
        }
        /// <summary>
        /// Get List Permission By ParentID
        /// </summary>
        /// <param name="parentID"></param>
        /// <returns></returns>
        public List<SCSService.Security_Permission> GetListSecurityPermissionByParentID(int? parentID)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Security_Permissions
                            select p);

                if (parentID != null)
                {
                    list = list.Where(p => p.ParentID==parentID);
                }

                return list.ToList();
            }
        }
        /// <summary>
        /// Create Security Role Permission
        /// </summary>
        /// <param name="srp"></param>
        public void CreateSecurityRolePermission(SCSService.Security_Role_Permission srp)
        {
            using (var data = new DataDataContext())
            {
                data.Security_Role_Permissions.InsertOnSubmit(srp);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Delete Security role Permission
        /// </summary>
        /// <param name="perID"></param>
        /// <param name="roleID"></param>
        public void DeleteSecurityRolePermission(int perID,int roleID)
        {
            using (var data = new DataDataContext())
            {
                var rolePerDelete = (from p in data.Security_Role_Permissions
                                     where p.PerID == perID && p.RoleID == roleID
                                     select p).FirstOrDefault();

                data.Security_Role_Permissions.DeleteOnSubmit(rolePerDelete);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// check Security role Permission exists (false is exists , true is not exist)
        /// </summary>
        /// <param name="perID"></param>
        /// <param name="roleID"></param>
        /// <returns></returns>
        public bool CheckRolePermissionExists(int perID, int roleID)
        {
            using (var data = new DataDataContext())
            {
                bool result = false;
                var rolePerDelete = (from p in data.Security_Role_Permissions
                                     where p.PerID == perID && p.RoleID == roleID
                                     select p);

                if (rolePerDelete.Count() > 0)
                {
                    result = false;
                }
                else
                    result = true;

                return result;
            }
        }
        /// <summary>
        /// Get List Permission by UserID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<SCSService.Security_Permission> GetListPermissionByUser(int id)
        {
            using (var data = new DataDataContext())
            {
                var per = (from u in data.Security_Users
                           join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                           from t0 in e0.DefaultIfEmpty()
                           join rp in data.Security_Role_Permissions on t0.RoleID equals rp.RoleID into e1
                           from t1 in e1.DefaultIfEmpty()
                           join p in data.Security_Permissions on t1.PerID equals p.PerID into e2
                           from t2 in e2.DefaultIfEmpty()
                           where u.UserID == id
                           orderby t0.Priority ascending
                           select t2);

                return per.ToList();
            }
        }
        /// <summary>
        /// Update Uer Log
        /// </summary>
        /// <param name="log"></param>
        public void UpdateUserLog(SCSService.Security_User_Log log)
        {
            using (var data = new DataDataContext())
            {
                var logUpdate = data.Security_User_Logs.Where(p => p.LogID == log.LogID).SingleOrDefault();

                //logUpdate.UserID = log.UserID;
                logUpdate.LogoutDate = log.LogoutDate;
                logUpdate.LogFile = log.LogFile;
                logUpdate.LogDetail = log.LogDetail;
                logUpdate.IpLog = log.IpLog;

                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Delete User Log
        /// </summary>
        /// <param name="id"></param>
        public void DeletetUserLog(int id)
        {
            using (var data = new DataDataContext())
            {
                var logDelete = data.Security_User_Logs.Where(p => p.LogID == id).SingleOrDefault();

                data.Security_User_Logs.DeleteOnSubmit(logDelete);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Get List Child of User Login in siteGuid
        /// </summary>
        /// <param name="idLogin"></param>
        /// <param name="priorityLogin"></param>
        /// <param name="siteGuid"></param>
        /// <param name="UserName"></param>
        /// <param name="Email"></param>
        /// <param name="UserStatus"></param>
        /// <returns></returns>
        public List<SCSService.Security_User> GetListUserByUserIDLogin(int? idLogin, int? priorityLogin,int? siteGuid, string UserName, string Email, byte? UserStatus)
        {
            using (var data = new DataDataContext())
            {
                if (siteGuid != null)
                {
                    var list = (from u in data.Security_Users
                                join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                                from t0 in e0.DefaultIfEmpty()
                                join rp in data.Security_Role_Permissions on t0.RoleID equals rp.RoleID into e1
                                from t1 in e1.DefaultIfEmpty()
                                join p in data.Security_Permissions on t1.PerID equals p.PerID into e2
                                from t2 in e2.DefaultIfEmpty()
                                join swu in data.Security_Website_Users on u.UserID equals swu.UserID into e3
                                from t3 in e3.DefaultIfEmpty()
                                where t0.Priority > priorityLogin && u.UserID != idLogin && t3.SiteGuid == siteGuid
                                orderby t0.Priority ascending
                                select u);

                    if (UserName != string.Empty && UserName != null)
                    {
                        list = list.Where(p => p.UserName.Contains(UserName));
                    }

                    if (Email != string.Empty && Email != null)
                    {
                        list = list.Where(p => p.Email.Contains(Email));
                    }

                    if (UserStatus != null)
                    {
                        list = list.Where(p => p.UserStatus == UserStatus);
                    }

                    return list.ToList();
                }
                else
                {
                    var list = (from u in data.Security_Users
                                join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                                from t0 in e0.DefaultIfEmpty()
                                join rp in data.Security_Role_Permissions on t0.RoleID equals rp.RoleID into e1
                                from t1 in e1.DefaultIfEmpty()
                                join p in data.Security_Permissions on t1.PerID equals p.PerID into e2
                                from t2 in e2.DefaultIfEmpty()
                                join swu in data.Security_Website_Users on u.UserID equals swu.UserID into e3
                                from t3 in e3.DefaultIfEmpty()
                                where t0.Priority > priorityLogin && u.UserID != idLogin
                                orderby t0.Priority ascending
                                select u);

                    if (UserName != string.Empty && UserName != null)
                    {
                        list = list.Where(p => p.UserName.Contains(UserName));
                    }

                    if (Email != string.Empty && Email != null)
                    {
                        list = list.Where(p => p.Email.Contains(Email));
                    }

                    if (UserStatus != null)
                    {
                        list = list.Where(p => p.UserStatus == UserStatus);
                    }

                    return list.ToList();
                }
                
            }
        }
        /// <summary>
        /// Update UserStatus
        /// </summary>
        /// <param name="id"></param>
        /// <param name="newStatus"></param>
        public void UpdateUserStatus(int? id, int? newStatus)
        {
            using (var data = new DataDataContext())
            {
                if (id != null && newStatus != null)
                {
                    var users = (from p in data.Security_Users
                                   where p.UserID == id
                                   select p).FirstOrDefault();
                    if (users != null)
                    {
                        if (users.UserStatus != newStatus || users.UserStatus == null)
                            users.UserStatus = (byte)newStatus;

                        data.SubmitChanges();
                    }
                }

            }
        }

        /// <summary>
        /// Update Role status
        /// </summary>
        /// <param name="id"></param>
        /// <param name="newStatus"></param>
        public void UpdateRoleStatus(int? id, int? newStatus)
        {
            using (var data = new DataDataContext())
            {
                if (id != null && newStatus != null)
                {
                    var role = (from p in data.Security_Roles
                                 where p.RoleID == id
                                 select p).FirstOrDefault();
                    if (role != null)
                    {
                        if (role.RoleStatus != newStatus || role.RoleStatus == null)
                            role.RoleStatus = (byte)newStatus;

                        data.SubmitChanges();
                    }
                }

            }
        }
        /// <summary>
        /// Get List Permission
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<SCSService.Security_Permission> GetListPermission(string PerName, string PerDes, int? ParentID, byte? PerStatus,int? siteGuid)
        {
            using (var data = new DataDataContext())
            {
                if (siteGuid != null)
                {
                    var per = (from p in data.Security_Permissions
                               join swp in data.Security_Website_Permissions on p.PerID equals swp.PerID
                               where p.SiteGuidCreated==siteGuid
                               select p);

                    if (PerName != string.Empty && PerName != null)
                    {
                        per = per.Where(p => p.PerName.Contains(PerName));
                    }

                    if (PerDes != string.Empty && PerDes != null)
                    {
                        per = per.Where(p => p.PerDes.Contains(PerDes));
                    }

                    if (ParentID != null)
                    {
                        per = per.Where(p => p.ParentID == ParentID);
                    }

                    if (PerStatus != null)
                    {
                        per = per.Where(p => p.PerStatus == PerStatus);
                    }

                    return per.ToList();
                }
                else
                {
                    var per = (from p in data.Security_Permissions
                               select p);

                    if (PerName != string.Empty && PerName != null)
                    {
                        per = per.Where(p => p.PerName.Contains(PerName));
                    }

                    if (PerDes != string.Empty && PerDes != null)
                    {
                        per = per.Where(p => p.PerDes.Contains(PerDes));
                    }

                    if (ParentID != null)
                    {
                        per = per.Where(p => p.ParentID == ParentID);
                    }

                    if (PerStatus != null)
                    {
                        per = per.Where(p => p.PerStatus == PerStatus);
                    }

                    return per.ToList();
                }
                
            }
        }

        /// <summary>
        /// Update PermissionStatus
        /// </summary>
        /// <param name="id"></param>
        /// <param name="newStatus"></param>
        public void UpdatePermissionStatus(int? id, int? newStatus)
        {
            using (var data = new DataDataContext())
            {
                if (id != null && newStatus != null)
                {
                    var per = (from p in data.Security_Permissions
                                where p.PerID == id
                                select p).FirstOrDefault();
                    if (per != null)
                    {
                        if (per.PerStatus != newStatus || per.PerStatus == null)
                            per.PerStatus = (byte)newStatus;

                        data.SubmitChanges();
                    }
                }

            }
        }

        /// <summary>
        /// Get List Security role Permission by RoleID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<SCSService.Security_Role_Permission> GetListSecurityRolePermissionByRoleID(int id)
        {
            using (var data = new DataDataContext())
            {
                var list = (from u in data.Security_Users
                            join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                            from t0 in e0.DefaultIfEmpty()
                            join rp in data.Security_Role_Permissions on t0.RoleID equals rp.RoleID into e1
                            from t1 in e1.DefaultIfEmpty()
                            join p in data.Security_Permissions on t1.PerID equals p.PerID into e2
                            from t2 in e2.DefaultIfEmpty()
                            where t1.RoleID==id
                            orderby t0.Priority ascending
                            select t1);

                
                return list.ToList();
            }
        }
        /// <summary>
        /// Get List Role in system by priority of User Login
        /// </summary>
        /// <param name="priority"></param>
        /// <returns></returns>
        public List<SCSService.Security_Role> GetListRoleByPriority(int priority,int? siteGuid)
        {
            using (var data = new DataDataContext())
            {
                if (siteGuid != null)
                {
                    var list = (from p in data.Security_Roles
                                join swr in data.Sercurity_Website_Roles on p.RoleID equals swr.RoleID
                                where p.Priority > priority && swr.SiteGuid==siteGuid
                                select p);
                    return list.ToList();
                }
                else
                {
                    var list = (from p in data.Security_Roles
                                where p.Priority > priority
                                select p);
                    return list.ToList();
                }
                
            }
        }
        /// <summary>
        /// Delete User
        /// </summary>
        /// <param name="id"></param>
        public void DeleteUser(int id)
        {
            using (var data = new DataDataContext())
            {
                var users = (from p in data.Security_Users
                             where p.UserID == id
                             select p).FirstOrDefault();

                data.Security_Users.DeleteOnSubmit(users);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Get List Module Permission by PerID
        /// </summary>
        /// <param name="PerId"></param>
        /// <returns></returns>
        public List<SCSService.Security_Module_Permission> GetListModulePermissionByPerID(int PerId)
        {
            using (var data = new DataDataContext())
            {
                var item = data.Security_Module_Permissions.Where(p => p.PerID == PerId).ToList();
                return item;
            }
        }
        /// <summary>
        /// Delete modle permission
        /// </summary>
        /// <param name="moduleID"></param>
        /// <param name="perID"></param>
        public void DeleteModulePermission(int moduleID,int perID)
        {
            using (var data = new DataDataContext())
            {
                var modulePermission = (from p in data.Security_Module_Permissions
                             where p.ModulID == moduleID && p.PerID == perID
                             select p).FirstOrDefault();

                data.Security_Module_Permissions.DeleteOnSubmit(modulePermission);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Create Module Permission
        /// </summary>
        /// <param name="smp"></param>
        public void CreateModulePermission(SCSService.Security_Module_Permission smp)
        {
            using (var data = new DataDataContext())
            {
                data.Security_Module_Permissions.InsertOnSubmit(smp);
                data.SubmitChanges();
            }
        }

        /// <summary>
        /// Get list PagePermission by PerID
        /// </summary>
        /// <param name="PerId"></param>
        /// <returns></returns>
        public List<SCSService.Security_Page_Permission> GetListPagePermissionByPerID(int PerId)
        {
            using (var data = new DataDataContext())
            {
                var item = data.Security_Page_Permissions.Where(p => p.PerID == PerId).ToList();
                return item;
            }
        }
        /// <summary>
        /// Delete Page Permission
        /// </summary>
        /// <param name="pageID"></param>
        /// <param name="perID"></param>
        public void DeletePagePermission(int pageID, int perID)
        {
            using (var data = new DataDataContext())
            {
                var pagePermission = (from p in data.Security_Page_Permissions
                                        where p.PageID == pageID && p.PerID == perID
                                        select p).FirstOrDefault();

                data.Security_Page_Permissions.DeleteOnSubmit(pagePermission);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Create Page Permission
        /// </summary>
        /// <param name="spp"></param>
        public void CreatePagePermission(SCSService.Security_Page_Permission spp)
        {
            using (var data = new DataDataContext())
            {
                data.Security_Page_Permissions.InsertOnSubmit(spp);
                data.SubmitChanges();
            }
        }

        /// <summary>
        /// Update customer
        /// </summary>
        /// <param name="cus"></param>
        public void UpdateCustomer(Customer cus)
        {
            using (var data = new DataDataContext())
            {
                var customer = data.Customers.Where(p => p.UserID == cus.UserID).FirstOrDefault();

                customer.CustomerID = cus.CustomerID;
                customer.CustomerName = cus.CustomerName;
                customer.CustomerCode = cus.CustomerCode;
                customer.Email = cus.Email;
                customer.CompanyName = cus.CompanyName;
                customer.CityID = cus.CityID;
                customer.Balance = cus.Balance;
                customer.Address = cus.Address;
                customer.ActiveDate = cus.ActiveDate;
                customer.CustomerType = cus.CustomerType;
                customer.DistricID = cus.DistricID;
                customer.Mobile = cus.Mobile;
                customer.PrecintID = cus.PrecintID;
                customer.Phone = cus.Phone;
                customer.Status = cus.Status;
                customer.TypeBussiness = cus.TypeBussiness;
                customer.UserID = cus.UserID;

                data.SubmitChanges();
            }
        }

        /// <summary>
        /// Get List Order for Customer
        /// </summary>
        /// <param name="id"></param>
        /// <param name="status"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public List<Customer_Order> GetListOrderByCustomerID(int id, int? status, DateTime? startDate, DateTime? endDate, int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var listOrder = (from p in data.Customer_Orders
                                 where p.CustomerID == id && p.SiteGuid == siteGuid
                                 select p);

                if (status != null)
                {
                    listOrder = listOrder.Where(p => p.OrderStatus == status);
                }

                if (startDate != null && endDate == null)
                {
                    listOrder = listOrder.Where(p => p.CreateDate >= startDate);
                }

                if (startDate == null && endDate != null)
                {
                    listOrder = listOrder.Where(p => p.CreateDate <= endDate);
                }

                if (startDate != null && endDate != null)
                {
                    listOrder = listOrder.Where(p => p.CreateDate >= startDate && p.CreateDate <= endDate);
                }

                listOrder = listOrder.OrderByDescending(p => p.OrderID);

                return listOrder.ToList();
            }
        }

        public List<Detail_Order> CustomerOrderDetail(int? id)
        {
            using (var data = new DataDataContext())
            {


                List<Detail_Order> result = (from p in data.Customer_Orders
                                             join ot in data.Order_Items on p.OrderID equals ot.OrderID into r0
                                             from e0 in r0
                                             join oid in data.Order_Item_Details on e0.OrderItemID equals oid.OrderItemID into r2
                                             from e1 in r2.DefaultIfEmpty()
                                             join psd in data.Product_Stock_Details on e1.ProductStockDetailID equals psd.ProductStockDetailID into r3
                                             from e2 in r3.DefaultIfEmpty()
                                             where p.OrderID == id
                                             select new Detail_Order
                                             {
                                                 OrderID = p.OrderID,
                                                 CreateDate = p.CreateDate,
                                                 FullName = p.Fullname,
                                                 StatusOrder = p.OrderStatus,
                                                 Total = p.Total,
                                                 Email = p.Email,
                                                 Phone = p.Phone,
                                                 ProductID = e0.ProductID,
                                                 OrderItemID = e0.OrderItemID,
                                                 Code = e2.Code,
                                                 Serial = e2.Serial
                                             }).ToList();
                return result;
            }

        }
    }
}
